package com.ruoyi.web.controller.monitor;

import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

public class ExcelToHtml {
    public static void main(String[] args) throws IOException {
        String excelFilePath = "e:/t.xlsx";
        try (InputStream is = new FileInputStream(excelFilePath)) {
            Workbook workbook = new XSSFWorkbook(is);
            Sheet sheet = workbook.getSheetAt(0);

            StringBuilder htmlBuilder = new StringBuilder();
            htmlBuilder.append("<html><body>");

            for (Row row : sheet) {
                htmlBuilder.append("<p>");
                for (Cell cell : row) {
                    if(cell.getCellType()!=CellType.NUMERIC){
                        String cellValue = getCellValueAsHtml(workbook,cell);
                        htmlBuilder.append(cellValue);
                        htmlBuilder.append(" ");
                    }

                }
                htmlBuilder.append("</p>");
            }

            htmlBuilder.append("</body></html>");
            System.out.println(htmlBuilder.toString());
        }
    }

    private static String getCellValueAsHtml(Workbook workbook ,Cell cell) {
        StringBuilder cellValue = new StringBuilder();

        XSSFRichTextString xssfRichText = (XSSFRichTextString) cell.getRichStringCellValue();
        for (int i = 0; i < xssfRichText.numFormattingRuns(); i++) {
            int start = xssfRichText.getIndexOfFormattingRun(i);
            int end = xssfRichText.length();
            String textHead = "";
            if (i + 1 < xssfRichText.numFormattingRuns()) {
                end = xssfRichText.getIndexOfFormattingRun(i + 1);
            }
            String  textContent = xssfRichText.getString().substring(start, end);
            //XSSFRichTextString.numFormattingRuns()获取不到富文本头文本样式，这时候文本样式取单元格样式
            if(start == 0){
                Font font = workbook.getFontAt(cell.getCellStyle().getFontIndexAsInt());
                textContent = unionHtmlStyleFormFont(font, textContent, textHead);
            }else{
                XSSFFont font = xssfRichText.getFontAtIndex(start);
                textContent = unionHtmlStyleFormFont(font, textContent, textHead);
            }
            cellValue.append(textContent);
        }
        return cellValue.toString();
    }
    /**
     * 拼接html样式（根据具体业务需求实现）
     */
    private static String unionHtmlStyleFormFont(Font font, String textContent, String textHead){
        if(ObjectUtils.isNotEmpty(font)){
            //加粗
            if(font.getBold()){
                textContent = "<strong>" + textContent + "</strong>";
            }
            //斜体
            if(font.getItalic()){
                textContent = "<em>" + textContent + "</em>";
            }
            //上下标
            switch (font.getTypeOffset()){
                case Font.SS_SUPER:
                    textContent = "<sup>" + textContent + "</sup>";
                    break;
                case Font.SS_SUB:
                    textContent = "<sub>" + textContent + "</sub>";
                default:
                    break;
            }
            //水平删除斜线
            if(font.getStrikeout()){
                textContent = "<span style=\"text-decoration: line-through;\">" + textContent + "</span>";
            }
            //下划线
            if(font.getUnderline() == Font.U_SINGLE){
                textContent = "<span style=\"text-decoration: underline;\">" + textContent + "</span>";
            }
        }
        if(StringUtils.isNotBlank(textHead)){
            textContent = textHead + textContent;
        }
        return textContent;
    }
}